﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Ebiz.libs;
using System.Web.Script.Services;
using System.Web.Services;
using System.Data.SqlClient;

namespace Ebiz
{
    public partial class AddRules : System.Web.UI.Page
    {
        private enum LimitMode {
            LimitEQ, LimitLW, LimitUP, LimitLE, LimitUE
        }
        private enum TestMode { 
            EQ, LW, LE, UP, UE
        }
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> LoadRules(){
            List<object> result = new List<object>();
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQry = "SELECT RulePrefix, Description FROM mstRulePrefix " +
                    "ORDER BY IDSeq";
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                if (sqlRdr.HasRows){
                    while (sqlRdr.Read()){
                        string strKey = sqlRdr.GetValue(0).ToString();
                        string strVal = sqlRdr.GetValue(1).ToString();

                        Dictionary<string, object> lstRules = new Dictionary<string, object>();
                        lstRules.Add("RuleCode", strKey);
                        lstRules.Add("RuleInfo", strVal);

                        result.Add(lstRules);
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static Dictionary<string, object> RuleFormula(string RulePrefix){
            Dictionary<string, object> result = new Dictionary<string, object>();
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQry = "SELECT RulePrefix, Control1, Control2, OperatorNum, Operator1, " +
                    "Operator2 FROM mstRulePrefix WHERE RulePrefix = @RulePrefix";

                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                sqlCmd.Parameters.AddWithValue("@RulePrefix", RulePrefix);
                SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                if (sqlRdr.HasRows){
                    if (sqlRdr.Read()){
                        string strCtrlTxt1 = sqlRdr.GetValue(1).ToString();
                        string strCtrlTxt2 = sqlRdr.GetValue(2).ToString();
                        string strSym1 = sqlRdr.GetValue(4).ToString();
                        string strSym2 = sqlRdr.GetValue(5).ToString();

                        List<string> Ctrls = new List<string>();
                        List<string> Operators = new List<string>();
                        Ctrls.Add(strCtrlTxt1);
                        Operators.Add(strSym1);
                        if (!(string.IsNullOrEmpty(strCtrlTxt2))){
                            Ctrls.Add(strCtrlTxt2);
                            Operators.Add(strSym2);
                        }

                        result.Add("Controls", Ctrls);
                        result.Add("Operators", Operators);
                        result.Add("Contents", GetChannel(strCtrlTxt1));
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        private static List<object> GetChannel(string str){
            List<object> result = new List<object>();
            if (str == "Channel"){
                string strCon = DBUtilities.strConnection("devCon");
                SqlConnection sqlCon = new SqlConnection(strCon);
                using (sqlCon){
                    sqlCon.Open();

                    string strQry = "SELECT IDChannel, ChannelName FROM mstChannel";
                    SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                    SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                    if (sqlRdr.HasRows){
                        while (sqlRdr.Read()){
                            string strKey = sqlRdr.GetValue(0).ToString();
                            string strVal = sqlRdr.GetValue(1).ToString();

                            Dictionary<string, object> tmpResult = new Dictionary<string, object>();
                            tmpResult.Add("ID", strKey);
                            tmpResult.Add("CHName", strVal);

                            result.Add(tmpResult);
                        }
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<string> RuleDetails(string RulePrefix, string Operator1, string Operator2){
            List<string> result = new List<string>();
            string strCon = DBUtilities.strConnection("devCon");
            SqlConnection sqlCon = new SqlConnection(strCon);
            using (sqlCon){
                sqlCon.Open();

                string strQry = "SELECT ControlCode, (SELECT COUNT(IDControl) AS tScenario " +
                    "FROM tbl_ApprovalRule WHERE (IDControl = mstRuleCode.ControlCode)) AS TotalScenario " +
                    "FROM mstRuleCode WHERE " +
                    "(ControlPrefix = @RulePrefix) AND (OPSys1 = @Operator1) " +
                    "AND (OPSys2 = @Operator2)";

                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                sqlCmd.Parameters.AddWithValue("@RulePrefix", RulePrefix);
                sqlCmd.Parameters.AddWithValue("@Operator1", Operator1);
                sqlCmd.Parameters.AddWithValue("@Operator2", Operator2);

                SqlDataReader sqlRdr = sqlCmd.ExecuteReader();
                if (sqlRdr.HasRows){
                    if (sqlRdr.Read()){
                        string RuleCode = sqlRdr.GetValue(0).ToString();
                        string TotalRules = sqlRdr.GetValue(1).ToString();

                        result.Add(RuleCode);
                        result.Add(TotalRules);
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> CreateRule(List<string> RuleParams, List<string> Operator, List<string> OptValue)
        {
            List<object> result = new List<object>();
            bool tmpResult = false;

            string[] _RuleParams = RuleParams[0].Split(',');
            string[] _Operator = Operator[0].Split(',');
            string[] _Values = OptValue[0].Split(',');

            string IDControl = _RuleParams[1].ToString();
            string Description = _RuleParams[2].ToString();
            string KeyLayer = _RuleParams[3].ToString();
            string MaxLayer = _RuleParams[4].ToString();
            string OverDisc = _RuleParams[5].ToString();

            int optLen = _Operator.Length;
            string sym1 = (optLen >= 1) ? _Operator[0].ToString() : "";
            string sym2 = (optLen > 1) ? _Operator[1].ToString() : "";

            int valLen = _Values.Length;
            string val1 = (valLen >= 1) ? _Values[0].ToString() : "";
            string val2 = (valLen > 1) ? _Values[1].ToString() : "";

            string CtrlPrefix = IDControl.Substring(0, 2);
            List<string> SuccessExt = new List<string>();
            if ((CtrlPrefix == "CH") || (CtrlPrefix == "CD" && IsNumeric(val2) == true) || (CtrlPrefix == "DC" && IsNumeric(val1) == true))
            {
                string affField = AffectedField(IDControl);
                string affValue = AffectedValue(IDControl, val2, MaxLayer);

                int Scenario = 0;
                switch (IDControl){
                    case "CHAN":
                    case "DCLW":
                    case "DCLE":
                    case "DCEQ":
                    case "DCUP":
                    case "DCUE":
                        Scenario = ScenarioNumber(IDControl, val1, sym1);
                        if (Scenario > 1){
                            if (AddNewRule(IDControl, Scenario, sym1, val1, Description, KeyLayer, MaxLayer, OverDisc, affField, affValue) > 0){
                                result.Add("success");
                                SuccessExt.Add(IDControl);
                                SuccessExt.Add(val1);
                                SuccessExt.Add("");
                                SuccessExt.Add(Scenario.ToString());
                                result.Add(SuccessExt);
                            }else{
                                result.Add("error");
                                SuccessExt.Add("Update data failed!");
                                result.Add(SuccessExt);
                            }
                        }else{
                            tmpResult = isValid(IDControl, val1, val2);
                            if (tmpResult == true){
                                if (AddNewRule(IDControl, Scenario, sym1, val1, Description, KeyLayer, MaxLayer, OverDisc, affField, affValue) > 0)
                                {
                                    result.Add("success");
                                    SuccessExt.Add(IDControl);
                                    SuccessExt.Add(val1);
                                    SuccessExt.Add("");
                                    SuccessExt.Add(Scenario.ToString());
                                    result.Add(SuccessExt);
                                }else{
                                    result.Add("error");
                                    SuccessExt.Add("Update data failed!");
                                    result.Add(SuccessExt);
                                }
                            }else{
                                result.Add("error");
                                SuccessExt.Add("Update data failed!\n\nInvalid Rule Conditions is detected.");
                                result.Add(SuccessExt);
                            }
                        }
                        break;
                    case "CDEQ":
                    case "CDLW":
                    case "CDLE":
                    case "CDUP":
                    case "CDUE":
                        Scenario = ScenarioNumber(IDControl, val1, sym1, sym2, val2);
                        if (Scenario > 1){
                            if (AddNewRule(IDControl, Scenario, sym1, val1, sym2, val2, Description, KeyLayer, MaxLayer, OverDisc, affField, affValue) > 0){
                                result.Add("success");
                                SuccessExt.Add(IDControl);
                                SuccessExt.Add(val1);
                                SuccessExt.Add(val2);
                                SuccessExt.Add(Scenario.ToString());
                                result.Add(SuccessExt);
                            }else{
                                result.Add("error");
                                SuccessExt.Add("Update data failed!");
                                result.Add(SuccessExt);
                            }
                        }else{
                            tmpResult = isValid(IDControl, val1, val2);
                            if (tmpResult == true){
                                string xxx = ScenarioNumber("CHAN", val1, sym1).ToString();
                                if (AddNewRule(IDControl, Scenario, sym1, val1, sym2, val2, Description, KeyLayer, MaxLayer, OverDisc, affField, affValue) > 0){
                                    result.Add("success");
                                    SuccessExt.Add(IDControl);
                                    SuccessExt.Add(val1);
                                    SuccessExt.Add(val2);
                                    SuccessExt.Add(Scenario.ToString());
                                    result.Add(SuccessExt);
                                }else{
                                    result.Add("error");
                                    SuccessExt.Add("Update data failed!");
                                    result.Add(SuccessExt);
                                }
                            }else{
                                result.Add("error");
                                SuccessExt.Add("Update data failed!\n\nInvalid Rule Conditions is detected.");
                                result.Add(SuccessExt);
                            }
                        }
                        break;
                }
            }
            else{
                result.Add("error");
                SuccessExt.Add("Update data failed!");
                result.Add(SuccessExt);
            }
            return result;
        }

        private static bool IsNumeric(string value){
            double dbNum;
            return (Double.TryParse(value, out dbNum));
        }

        private static string AffectedField(string IDControl){
            string result = "";
            switch (IDControl){
                case "CHAN":
                case "CDEQ":
                case "CDLW":
                case "CDLE":
                case "CDUP":
                case "CDUE":
                    result = "DISCOUNT";
                    break;
                case "DCEQ":
                case "DCLW":
                case "DCLE":
                case "DCUP":
                case "DCUE":
                    result = "APPLAYER";
                    break;
            }
            return result;
        }

        private static string AffectedValue(string IDControl, string Value2, string TotalLayers){
            string result = "";
            switch (IDControl){
                case "CHAN":
                    result = "0";
                    break;
                case "CDEQ":
                case "CDLW":
                case "CDLE":
                case "CDUP":
                case "CDUE":
                    result = Value2;
                    break;
                case "DCEQ":
                case "DCLW":
                case "DCLE":
                case "DCUP":
                case "DCUE":
                    result = TotalLayers;
                    break;
            }
            return result;
        }

        private static int AddNewRule(string IDControl, int Scenario, string OptSign,
            string OptValue, string Description, string KeyLayer, string MaxLayer, string FixDisc,
            string AffField, string AffValue){

            int result = 0;
            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
            string CreatedDate = DateTime.Today.ToString("yyyy/MM/dd HH:mm:ss");

            string strQry = "INSERT INTO tbl_ApprovalRule (IDControl, Scenario, Operator1, " +
            "Value1, Description, CreatedBy, CreatedDate, KeyLayer, TotalLayer, FixDisc, AffectedField, " +
            "AffectedValue) VALUES ('" +
            IDControl + "', " + Scenario + ", '" + OptSign + "', '" + OptValue + "', '" + Description +
            "', '" + CreatedBy + "', '" + CreatedDate + "', " + KeyLayer + ", " + MaxLayer +
            ", " + FixDisc + ", '" + AffField + "', '" + AffValue + "')";

            result = UpdateData(strQry);

            return result;
        }

        private static int AddNewRule(string IDControl, int Scenario, string OptSign, string OptValue,
            string OptSignExt, string OptValueExt, string Description, string KeyLayer, string MaxLayer,
            string FixDisc, string AffField, string AffValue){
            int result = 0;

            string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
            string CreatedDate = DateTime.Today.ToString("yyyy/MM/dd HH:mm:ss");

            string strQry = "INSERT INTO tbl_ApprovalRule (IDControl, Scenario, Operator1, " +
            "Value1, Operator2, Value2, Description, CreatedBy, CreatedDate, KeyLayer, " +
            "TotalLayer, FixDisc, AffectedField, AffectedValue) VALUES ('" + IDControl + "', " +
            Scenario + ", '" + OptSign + "', '" + OptValue + "'," + "'" + OptSignExt + "', '" +
            OptValueExt + "', '" + Description + "', '" + CreatedBy + "', '" + CreatedDate + "', " +
            KeyLayer + ", " + MaxLayer + ", " + FixDisc + ", '" + AffField + "', '" + AffValue + "')";
            result = UpdateData(strQry);

            return result;
        }

        private static int UpdateData(string strQuery){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                cn.Open();
                SqlCommand cmd = new SqlCommand(strQuery, cn);
                result = cmd.ExecuteNonQuery();
            }
            cn.Close();

            return result;
        }

        private static bool isValid(string IDControl, string OptValue1, string OptValue2){
            bool result = false;
            switch (IDControl){
                case "CHAN":
                    result = isValidCH(IDControl, OptValue1, OptValue2);
                    break;
                case "DCLW":
                case "DCLE":
                case "DCEQ":
                case "DCUP":
                case "DCUE":
                    result = isValidDC(IDControl, OptValue1);
                    break;
                case "CDEQ":
                case "CDLW":
                case "CDLE":
                case "CDUP":
                case "CDUE":
                    result = isValidCD(IDControl, OptValue1, OptValue2);
                    break;
            }
            return result;
        }

        private static bool isValidCH(string IDControl, string OptValue, string OptValueExt){
            bool result = false;
            string sCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(sCon);
            using (cn){
                cn.Open();
                string qry = "SELECT COUNT(IDControl) FROM tbl_ApprovalRule WHERE " +
                    "(IDControl = @IDControl) AND (Value1 = @OptValue) AND (Value2 = @OptValueExt)";
                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@IDControl", IDControl);
                cmd.Parameters.AddWithValue("@OptValue", OptValue);
                cmd.Parameters.AddWithValue("@OptValueExt", OptValueExt);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        int tmp = Convert.ToInt32(rdr.GetValue(0).ToString());
                        if (tmp <= 0){
                            result = true;
                        }
                    }
                }
                cn.Close();
            }
            return result;
        }

        private static bool isValidDC(string IDControl, string OptValue){
            bool result = false;

            double optVal = Convert.ToDouble(OptValue);
            string sCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(sCon);
            using (cn){
                cn.Open();
                string qryExt = "";
                qryExt = "SELECT Operator1, Value1 FROM tbl_ApprovalRule WHERE " +
                    "(SUBSTRING(IDControl, 0, 3) = '" + IDControl.Substring(0, 2) + "')";

                SqlCommand cmdExt = new SqlCommand(qryExt, cn);
                SqlDataReader rdrExt = cmdExt.ExecuteReader();
                List<string> lstResult = new List<string>();

                if (rdrExt.HasRows){
                    while (rdrExt.Read()){
                        string tmpA = rdrExt.GetValue(0).ToString();
                        double tmpB = Convert.ToDouble(rdrExt.GetValue(1).ToString());
                        switch (IDControl){
                            case "DCEQ":
                                lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.EQ));
                                break;
                            case "DCLW":
                                lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.LW));
                                break;
                            case "DCLE":
                                lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.LE));
                                break;
                            case "DCUP":
                                lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.UP));
                                break;
                            case "DCUE":
                                lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.UE));
                                break;
                        }
                    }
                }
                cn.Close();

                result = (lstResult.Contains("bad")) ? false : true;
            }
            return result;
        }

        private static bool isValidCD(string IDControl, string OptValue1, string OptValue2){
            bool result = false;
            if ((ScenarioNumber("CHAN", OptValue1, "=") <= 1) && ScenarioNumber(IDControl, OptValue1, "=") <= 1){
                string sCon = DBUtilities.strConnection("devCon");
                SqlConnection cn = new SqlConnection(sCon);
                using (cn){
                    cn.Open();
                    string qryExt = "SELECT Value1, Operator2, Value2 FROM tbl_ApprovalRule WHERE " +
                        "(Value1 = '" + OptValue1 + "') AND (SUBSTRING(IDControl, 0, 3) = '" +
                            IDControl.Substring(0, 2) + "')";

                    SqlCommand cmdExt = new SqlCommand(qryExt, cn);
                    SqlDataReader rdrExt = cmdExt.ExecuteReader();
                    List<string> lstResult = new List<string>();
                    double optVal = Convert.ToDouble(OptValue2);
                    if (rdrExt.HasRows){
                        while (rdrExt.Read()){
                            string tmpTxt = rdrExt.GetValue(1).ToString();
                            string tmpNum = rdrExt.GetValue(2).ToString();

                            string tmpA = (string.IsNullOrEmpty(tmpTxt)) ? "" : tmpTxt;
                            double tmpB = (string.IsNullOrEmpty(tmpNum)) ? 0 : Convert.ToDouble(tmpNum);
                            switch (IDControl){
                                case "CDEQ":
                                    lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.EQ));
                                    break;
                                case "CDLW":
                                    lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.LW));
                                    break;
                                case "CDLE":
                                    lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.LE));
                                    break;
                                case "CDUP":
                                    lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.UP));
                                    break;
                                case "CDUE":
                                    lstResult.Add(isValidRange(tmpA, optVal, tmpB, TestMode.UE));
                                    break;
                            }
                        }
                    }
                    cn.Close();
                    result = (lstResult.Contains("bad")) ? false : true;
                }
            }else{
                result = false;
            }
            return result;
        }

        private static bool isValidExt(string IDControl, string Operator, string OptValue){
            bool result = false;
            string sCon = DBUtilities.strConnection("devCon");
            SqlConnection cn = new SqlConnection(sCon);
            using (cn){
                cn.Open();
                string qry = "SELECT COUNT(IDControl) FROM tbl_ApprovalRule WHERE " +
                    "(IDControl = @IDControl) AND (Operator1 = @Operator) AND (Value1 = @OptValue)";
                SqlCommand cmd = new SqlCommand(qry, cn);
                cmd.Parameters.AddWithValue("@IDControl", IDControl);
                cmd.Parameters.AddWithValue("@Operator", Operator);
                cmd.Parameters.AddWithValue("@OptValue", OptValue);
                SqlDataReader rdr = cmd.ExecuteReader();
                if (rdr.HasRows){
                    if (rdr.Read()){
                        int tmp = Convert.ToInt32(rdr.GetValue(0).ToString());
                        if (tmp <= 0){
                            result = true;
                        }
                    }
                    cn.Close();
                }
            }
            return result;
        }

        private static string isValidRange(string optSign, double testNum, double vsNum, TestMode testMode){
            string result = "";
            switch (testMode){
                case TestMode.LW:
                    if (optSign == "=" && vsNum >= testNum){
                        result = "ok";
                    }else if (vsNum < testNum){
                        result = "bad";
                    }else if (optSign == "<" || optSign == "<="){
                        result = "bad";
                    }else if ((optSign == ">" || optSign == ">=") && vsNum >= testNum){
                        result = "ok";
                    }else{
                        result = "bad";
                    }
                    break;
                case TestMode.LE:
                    if (optSign == "=" && vsNum <= testNum){
                        result = "bad";
                    }else if (optSign == "=" && vsNum > testNum){
                        result = "ok";
                    }else if ((optSign == "<" || optSign == "<=") && vsNum > testNum){
                        result = "bad";
                    }else if ((optSign == ">" || optSign == ">=") && vsNum < testNum){
                        result = "bad";
                    }else if ((optSign == ">") && (vsNum > testNum || vsNum >= testNum)){
                        result = "ok";
                    }else if (optSign == ">=" && vsNum > testNum){
                        result = "ok";
                    }else if ((optSign == "<" || optSign == "<=") && vsNum < testNum){
                        result = "ok";
                    }else{
                        result = "bad";
                    }
                    break;
                case TestMode.UP:
                    if (optSign == "=" && vsNum <= testNum){
                        result = "ok";
                    }else if (optSign == ">" || optSign == ">="){
                        result = "bad";
                    }else if (vsNum > testNum){
                        result = "bad";
                    }else if ((optSign == "<" || optSign == "<=") && vsNum <= testNum){
                        result = "ok";
                    }else{
                        result = "bad";
                    }
                    break;
                case TestMode.UE:
                    if (optSign == "=" && vsNum >= testNum){
                        result = "bad";
                    }else if (optSign == "=" && vsNum < testNum){
                        result = "ok";
                    }else if ((optSign == "<" || optSign == "<=") && vsNum < testNum){
                        result = "ok";
                    }else if (optSign == ">" || optSign == ">="){
                        result = "bad";
                    }else if (vsNum > testNum){
                        result = "bad";
                    }else{
                        result = "bad";
                    }
                    break;
                case TestMode.EQ:
                    if (optSign == "=" && vsNum == testNum){
                        result = "bad";
                    }else if (optSign == "=" && (vsNum > testNum || vsNum < testNum)){
                        result = "ok";
                    }else if (optSign == ">" && vsNum >= testNum){
                        result = "ok";
                    }else if (optSign == ">" && vsNum <= testNum){
                        result = "bad";
                    }else if (optSign == "<" && vsNum <= testNum){
                        result = "ok";
                    }else if (optSign == "<" && vsNum >= testNum){
                        result = "bad";
                    }else if (optSign == "<=" && vsNum < testNum){
                        result = "ok";
                    }else if (optSign == ">=" && vsNum > testNum){
                        result = "ok";
                    }else{
                        result = "bad";
                    }
                    break;
            }
            return result;
        }

        private static int ScenarioNumber(string IDControl, string RuleValue, string OptSign){
            int result = 0;
            List<string> LstScenario = new List<string>();
            foreach (string strScenario in LoadScenario(IDControl, RuleValue, OptSign)){
                LstScenario.Add(strScenario);
            }

            int newIndex = 0;
            int TotalScenario = LstScenario.Count;
            if (TotalScenario > 0){
                bool isExist = false;
                while (isExist != true){
                    newIndex++;
                    string tmpIdx = newIndex.ToString();
                    if (LstScenario.Contains(tmpIdx)){
                        isExist = false;
                    }else{
                        isExist = true;
                    }
                }
                result = newIndex;
            }else{
                newIndex = 1;
                result = newIndex;
            }
            return result;
        }

        private static int ScenarioNumber(string IDControl, string RuleValue, string OptSign,
            string OptSignExt, string RuleValueExt){
            int result = 0;
            List<string> LstScenario = new List<string>();
            foreach (string strScenario in LoadScenario(IDControl, RuleValue, OptSign, RuleValueExt, OptSignExt))
            {
                LstScenario.Add(strScenario);
            }

            int newIndex = 0;
            int TotalScenario = LstScenario.Count;
            if (TotalScenario > 0){
                bool isExist = false;
                while (isExist != true){
                    newIndex++;
                    string tmpIdx = newIndex.ToString();
                    if (LstScenario.Contains(tmpIdx)){
                        isExist = false;
                    }else{
                        isExist = true;
                    }
                }
                result = newIndex;
            }else{
                newIndex = 1;
                result = newIndex;
            }
            return result;
        }

        private static List<string> LoadScenario(string IDControl, string RuleValue, string OptSign){
            List<string> result = new List<string>();

            string strQry = "SELECT IDControl, Scenario, Operator1, Value1 " +
                "FROM tbl_ApprovalRule WHERE (IDControl = @IDControl) " +
                "AND (Value1 = @Value1) AND (Operator1 = @Operator1)";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                sqlCmd.Parameters.AddWithValue("@IDControl", IDControl);
                sqlCmd.Parameters.AddWithValue("@Value1", RuleValue);
                sqlCmd.Parameters.AddWithValue("@Operator1", OptSign);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        result.Add(sqlReader.GetValue(1).ToString());
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        private static List<string> LoadScenario(string IDControl, string RuleValue, string OptSign,
            string OptSignExt, string RuleValueExt){
            List<string> result = new List<string>();

            string strQry = "SELECT IDControl, Scenario, Operator1, Value1 " +
                "FROM tbl_ApprovalRule WHERE (IDControl = @IDControl) " +
                "AND (Value1 = @Value1) AND (Operator1 = @Operator1) " +
                "AND (Operator2 = @Operator2) AND (Value2 = @Value2)";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                sqlCmd.Parameters.AddWithValue("@IDControl", IDControl);
                sqlCmd.Parameters.AddWithValue("@Value1", RuleValue);
                sqlCmd.Parameters.AddWithValue("@Operator1", OptSign);
                sqlCmd.Parameters.AddWithValue("@Value2", RuleValueExt);
                sqlCmd.Parameters.AddWithValue("@Operator2", OptSignExt);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        result.Add(sqlReader.GetValue(1).ToString());
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> LoadRole(string Search){
            List<object> result = new List<object>();
            string _Search = Func.Sanitize(Search);
            string strWhere = (string.IsNullOrEmpty(Search)) ? "" : " AND (RoleAlias LIKE '%" + _Search + "%')";

            string strQry = "SELECT IDRole, RoleAlias, RoleName FROM mstRole WHERE " +
                "(IDRole NOT IN (1,2))" + strWhere + " ORDER BY RoleAlias";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    while (sqlReader.Read()){
                        string strRoleID = sqlReader.GetValue(0).ToString();
                        string strRoleAlias = sqlReader.GetValue(1).ToString();
                        string strRoleName = sqlReader.GetValue(2).ToString();

                        Dictionary<string, object> dctRole = new Dictionary<string, object>();
                        dctRole.Add("ID", strRoleID);
                        dctRole.Add("Role", strRoleAlias);
                        dctRole.Add("Name", strRoleName);

                        result.Add(dctRole);
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> LoadUser(string Search){
            List<object> result = new List<object>();
            string _Search = Func.Sanitize(Search);
            string strWhere = (string.IsNullOrEmpty(Search)) ? "" : "AND (UserAccounts.FullName LIKE '%" + _Search + "%') ";

            string strQry = "SELECT UserAccounts.UserName, UserAccounts.FullName, mstRole.RoleAlias " +
                "FROM UserAccounts INNER JOIN mstRole ON UserAccounts.IDRole = mstRole.IDRole " +
                "WHERE (UserAccounts.IDRole NOT IN (1, 2)) AND (UserAccounts.StatusCode = '101') " + strWhere +
                "ORDER BY UserAccounts.FullName";
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                SqlCommand sqlCmd = new SqlCommand(strQry, sqlCon);
                SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                if (sqlReader.HasRows){
                    List<string> lstUsers = new List<string>();
                    while (sqlReader.Read()){
                        string strRole = sqlReader.GetValue(2).ToString();
                        string strUserName = sqlReader.GetValue(0).ToString();
                        string strFName = sqlReader.GetValue(1).ToString();

                        Dictionary<string, object> dctUsers = new Dictionary<string, object>();
                        dctUsers.Add("ID", strUserName);
                        dctUsers.Add("Role", strRole);
                        dctUsers.Add("Name", strFName);

                        result.Add(dctUsers);
                    }
                }
                sqlCon.Close();
            }
            return result;
        }

        [WebMethod(EnableSession = true)]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string UpdateLayer(string RuleControl, string RuleValue, string RuleValueExt, string Scenario,
            List<string> StepSeq, List<string> CurrentStep, List<string> NextStep, List<string> ActorCategory){

            string result = "";
            string[] tmpStepSeq = StepSeq[0].Split(',');
            string[] tmpCurrentStep = CurrentStep[0].Split(',');
            string[] tmpNextStep = NextStep[0].Split(',');
            string[] tmpActorCategory = ActorCategory[0].Split(',');

            string _IDControl = Func.Sanitize(RuleControl);
            string _RulePrefix = _IDControl.Substring(0, 2);
            string _Scenario = Func.Sanitize(Scenario);

            int uStepSeq = tmpStepSeq.Length;
            SqlConnection sqlCon = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (sqlCon){
                sqlCon.Open();
                // Delete all step category with same scenario
                string strQry1 = "DELETE FROM tbl_ApprovalStep WHERE (IDControl = '" + _IDControl + "') " +
                    "AND (Value1 = '" + RuleValue + "') AND (Scenario = " + _Scenario + ")";
                SqlCommand sqlCmd1 = new SqlCommand(strQry1, sqlCon);
                sqlCmd1.ExecuteNonQuery();
                sqlCon.Close();
                // End Delete Process

                //re-Inserting data
                string CreatedBy = Func.GetUserInfo(Func.ShowInfo._UserID);
                string CreatedDate = DateTime.Today.ToString("yyyy/MM/dd HH:mm:ss");
                for (int idx = 0; idx < uStepSeq; idx++){
                    sqlCon.Open();
                    string strQry2 = "INSERT INTO tbl_ApprovalStep (ControlPrefix, IDControl, Value1, Value2, Scenario, " +
                        "StepSeq, CurrentStep, NextStep, ActorCategory, CreatedBy, CreatedDate) VALUES (";

                    string sNextStep = tmpNextStep[idx];
                    string insertValues = "'" + _RulePrefix + "', '" + _IDControl + "', '" +
                       RuleValue + "', '" + RuleValueExt + "', '" + Scenario + "', '" + tmpStepSeq[idx] + "', '" +
                       tmpCurrentStep[idx] + "', '" + sNextStep + "', '" + tmpActorCategory[idx] + "', '" +
                       CreatedBy + "', '" + CreatedDate + "')";
                    strQry2 += insertValues;

                    SqlCommand sqlCmd2 = new SqlCommand(strQry2, sqlCon);
                    int AffectedRows = sqlCmd2.ExecuteNonQuery();
                    if (!(AffectedRows > 0)){
                        result = "Update data failed!";
                    }else{
                        result = "success";
                    }
                    sqlCon.Close();
                }
            }
            return result;
        }
    }
}
